﻿using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace EIS
{
    public class SqlServerHelper
    {
        public List<string> GetTables()
        {
            List<string> result = new List<string>();

            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MainConnectionString"].ConnectionString))
            {
                connection.Open();

                SqlCommand sqlCommand = new SqlCommand("sp_tables", connection)
                                            {CommandType = CommandType.StoredProcedure};

                SqlDataReader dr = sqlCommand.ExecuteReader();

                while (dr.Read())
                {
                    result.Add(dr["TABLE_NAME"].ToString());
                }
            }

            result.Sort();

            return result;
        }

        public static List<string> GetColumns(string tableName)
        {
            List<string> result = new List<string>();

            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MainConnectionString"].ConnectionString))
            {
                connection.Open();

                SqlCommand sqlCommand = new SqlCommand(string.Format(Resource.ColumnScript, tableName), connection) { CommandType = CommandType.Text };

                SqlDataReader dr = sqlCommand.ExecuteReader();

                while (dr.Read())
                {
                    result.Add(dr["Name"].ToString());
                }
            }
            result.Sort();

            return result;
        } 

        public string ExportDataTable(string name,string script)
        {
            StringBuilder resultBuilder=new StringBuilder();

            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MainConnectionString"].ConnectionString))
            {
                connection.Open();

                SqlDataAdapter sqlDataAdapter=new SqlDataAdapter(string.IsNullOrEmpty(script)?"select * from "+name:script, connection);

                DataSet dataSet=new DataSet();

                sqlDataAdapter.Fill(dataSet);

                if(dataSet.Tables.Count>0)
                {
                    foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                    {
                        resultBuilder.AppendFormat("Insert into {0} (", name);

                        for (int i=0;i<dataSet.Tables[0].Columns.Count;i++)
                        {
                            resultBuilder.AppendFormat("{0},", dataSet.Tables[0].Columns[i].ColumnName);
                        }
                        resultBuilder.Length--;
                        resultBuilder.Append(") values(");

                        for (int i = 0; i < dataSet.Tables[0].Columns.Count; i++)
                        {
                            resultBuilder.AppendFormat("'{0}',", dataRow[dataSet.Tables[0].Columns[i]].ToString().Replace("'", "''"));
                        }

                        resultBuilder.Length--;
                        resultBuilder.Append(")\r\n");
                    }
                }
            }

            return resultBuilder.ToString();
        }
    }
}
